<#----------------------------------------------------------------------------------
        NAME: Upsert-Database.ps1
       EMAIL: saleyoun@hotmail.com
 DESCRIPTION: This script inserts a record in the Database table in the iSQLPS system.
              If the record already exists, then this script updates the existing record.
------------------------------------------------------------------------------------
  DISCLAIMER: The AUTHOR ASSUMES NO RESPONSIBILITY FOR ANYTHING, including the 
  destruction of personal property, creating singularities, making deep fried chicken,
  causing your toilet to explode, making your animals spin around like mad, causing 
  hair loss, killing your buzz or ANYTHING else that can be thought up.
------------------------------------------------------------------------------------#>

[String] $strUpsertSql = ""
[String] $strQuerySql = ""

$strQuerySql = "SELECT O.HostName AS [SQLNetworkName], S.ServerSQLID, S.TCPPort
  FROM dbo.ServerSQL S 
 INNER JOIN dbo.ServerOS O ON S.ServerOSID = O.ServerOSID"

$sqlServers = Invoke-Sqlcmd -Query $strQuerySql -ServerInstance $iSQLPSServer -Database $iSQLPSDatabase

Foreach ($sqlServer in $sqlServers) {
	$strUpsertSql = ""

	$sqlNetworkName = $sqlServer.SQLNetworkName
	$sqlServerID = $sqlServer.ServerSQLID
	$sqlTcpPort = $sqlServer.TCPPort

	$sqlDatabases = Get-SQLDatabase $sqlNetworkName

	if ($sqlDatabases) {
		Foreach ($sqlDatabase in $sqlDatabases) {
			$strUpsertSql = $strUpsertSql + "EXEC UpsertDatabase " + $sqlServerID + ", '" + $sqlDatabase.name + "','"+ $sqlDatabase.Collation +"','"+ $sqlDatabase.CompatibilityLevel +"','"+ $sqlDatabase.CreateDate +"','"+ $sqlDatabase.RecoveryModel +"';`n"
		}
	}
	
	if ($strUpsertSql.Length -gt 0) {
		$strUpsertSql
		Invoke-Sqlcmd -Query $strUpsertSql -ServerInstance $iSQLPSServer -Database $iSQLPSDatabase 
	}
}
